﻿using MySqlConnector;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class SQLHelper
    {
        public static readonly string strconn = "server=localhost;database=studentclubmis;user=root ;password=123456 ;port=3306";
        // private static readonly Array para;

        public static DataTable ExecuteQuery(string strSQL)//查询数据表的数据存入数据集
        {
            MySqlConnection conn = new MySqlConnection(strconn);
            try
            {
                MySqlDataAdapter adapter = new MySqlDataAdapter(strSQL, conn);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
        public static DataTable ExecuteQuery(string strSQL, MySqlParameter[] para)
        {
            MySqlConnection conn = new MySqlConnection(strconn);
            try
            {
                MySqlCommand cmd = new MySqlCommand(strSQL, conn);
                cmd.Parameters.AddRange(para);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
        public static int ExecuteNonQuery(string strSQL)
        {
            MySqlConnection conn = new MySqlConnection(strconn);
            try
            {
                MySqlCommand cmd = new MySqlCommand(strSQL, conn);
                return (cmd.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        public static int ExecuteNonQuery(string strSQL, MySqlParameter[] para)
        {
            MySqlConnection con = new MySqlConnection(strconn);
            con.Open();
            try
            {
                MySqlCommand cmd = new MySqlCommand(strSQL, con);
                cmd.Parameters.AddRange(para);
                return (cmd.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }
    }

}
